package com.app.sqlite_connect;

import java.util.ArrayList;

import com.app.campusguide.R;
import android.content.ContentValues;
import android.content.Context;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.app.campusguide.Class_view;


public class HandlerSqlite extends SQLiteOpenHelper{
	private final String APP_LOG = "HandlerSqlite";
	Context ctx_;
	public HandlerSqlite (Context ctx)	{
		super(ctx,"Coord_DataBase_2.db",null,7);
		this.ctx_ = ctx;
	}
	
	
	@Override
	public void onCreate(SQLiteDatabase db)	{
		createTable_TypeSite(db);
		createTable_Site(db);
		createTable_Schedule(db);
		createView_ClassNowView(db);
		createView_NextClassView(db);
		createView_ScheduleTodayView(db);
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int i_new_version, int i_old_version)	{
		db.execSQL("DROP TABLE IF EXISTS SCHEDULE");
		db.execSQL("DROP TABLE IF EXISTS SITE");
		db.execSQL("DROP TABLE IF EXISTS TYPE_SITE");
		db.execSQL("DROP VIEW IF EXISTS class_now_view");
		db.execSQL("DROP VIEW IF EXISTS next_class_view");
		db.execSQL("DROP VIEW IF EXISTS createView_ScheduleTodayView");
		onCreate(db);
	}
	
	/**
	 * This method create, if not exists yet, the table schedule on database
	 * In column SCH_DAY_OF_WEEK the value are:
	 * 0 Sunday
     * 1 Monday
     * 2 Tuesday
     * 3 Wednesday
     * 4 Thursday
     * 5 Friday
	 * @param myDb	Database to work
	 */
	private void createTable_Schedule(SQLiteDatabase db){
		String str_qry = "CREATE TABLE SCHEDULE(" + "	SCH_ID INTEGER NOT NULL, "
				+ "	SCH_CLASS_NAME TEXT NOT NULL, "
				+ "	SCH_DAY_OF_WEEK INTEGER NOT NULL, "
				+ "	SCH_START_HOUR TEXT NOT NULL, "
				+ "	SCH_END_HOUR TEXT NOT NULL, " 
				+ "	SITE_ID INTEGER NOT NULL, "
				+ "	PRIMARY KEY (SCH_ID), " 
				+ "	FOREIGN KEY (SITE_ID) "
				+ "	REFERENCES SITE (SITE_ID)) ";
		Cursor crs = db.rawQuery("SELECT count(0) FROM sqlite_master WHERE type='table' AND name='SCHEDULE';", null);
		if(crs.moveToFirst())
		{
			if(crs.getInt(0) == 0)
			{
				db.execSQL(str_qry);
			}
		}
		crs = null;
	}
	
	/**
	 * This method create, if not exists yet, the table type_site on database
	 * @param myDb Database to work
	 */
	private void createTable_TypeSite(SQLiteDatabase db){
		String str_qry = "CREATE TABLE TYPE_SITE " +
				"(TYP_SITE_ID INTEGER NOT NULL, " +
				"TYP_SITE_NAME TEXT NOT NULL, " +
				"TYP_SITE_DESCRIPTION TEXT, " +
				"PRIMARY KEY (TYP_SITE_ID)) ";
		Cursor crs = db.rawQuery("SELECT count(0) FROM sqlite_master WHERE type='table' AND name='TYPE_SITE';", null);
		if(crs.moveToFirst())
		{
			if(crs.getInt(0) == 0)
			{
				db.execSQL(str_qry);
				insert_SiteType_values(db);
			}
		}
		crs = null;
	}
	
	/**
	 * This method create, if not exists yet, the table site on database
	 * @param myDb Database to work
	 */
	private void createTable_Site(SQLiteDatabase db){
		String str_qry = "CREATE TABLE [SITE] ( " +
				"[SITE_ID] INTEGER  NOT NULL, " +
				"[SITE_NAME] TEXT  NOT NULL, " +
				"[TYP_SITE_ID] INTEGER  NOT NULL, " +
				"[SITE_LOCATION_LATITUDE] TEXT  NOT NULL, " +
				"[SITE_LOCATION_LONGITUDE] TEXT  NOT NULL, " +
				"[SITE_LEVEL] INTEGER  NOT NULL) ";
		Cursor crs = db.rawQuery("SELECT count(0) FROM sqlite_master WHERE type='table' AND name='SITE';", null);
		if(crs.moveToFirst())
		{
			if(crs.getInt(0) == 0)
			{
				db.execSQL(str_qry);
				str_qry = "CREATE INDEX [IDX_SITE_NAME] ON [SITE]( " +
						"[SITE_NAME]  ASC)";
				db.execSQL(str_qry);
				insert_Site_values(db);
			}
		}
		crs = null;
	}
	
	
	/**
	 * This method create, if not exists yet, the view class_now_view on database
	 * This view allow see data of class that is in course
	 * @param myDb Database to work
	 */
	private void createView_ClassNowView(SQLiteDatabase db){
		String str_qry = "CREATE VIEW [class_now_view] AS " +
				"SELECT A.SCH_CLASS_NAME, " +
				"       A.SCH_START_HOUR, " +
				"       A.SCH_END_HOUR, " +
				"       B.SITE_NAME, " +
				"       B.SITE_LOCATION_LATITUDE, " +
				"       B.SITE_LOCATION_LONGITUDE, " +
				"       B.SITE_LEVEL " +
				"FROM   SCHEDULE A, SITE B " +
				"WHERE  A.SITE_ID = B.SITE_ID " +
				"AND    A.SCH_DAY_OF_WEEK = (strftime('%w','now','localtime')-1) " +
				"AND    datetime('now','localtime') between datetime(strftime('%Y','now','localtime')||'-'||strftime('%m','now','localtime')||'-'||strftime('%d','now','localtime')||' '||A.SCH_START_HOUR) " +
				"       and datetime(strftime('%Y','now','localtime')||'-'||strftime('%m','now','localtime')||'-'||strftime('%d','now','localtime')||' '||A.SCH_END_HOUR) ";
		Cursor crs = db.rawQuery("SELECT count(0) FROM sqlite_master WHERE type='view' AND name='class_now_view';", null);
		if(crs.moveToFirst())
		{
			if(crs.getInt(0) == 0)
			{
				db.execSQL(str_qry);
			}
		}
		crs = null;
	}
	
	/**
	 * This method create, if not exists yet, the view next_class_view on database
	 * This view allow see data of next class
	 * @param myDb Database to work
	 */
	private void createView_NextClassView(SQLiteDatabase db){
		String str_qry = "CREATE VIEW [next_class_view] AS " +
				"SELECT  A.SCH_CLASS_NAME, " +
				"        A.SCH_START_HOUR, " +
				"        A.SCH_END_HOUR, " +
				"        B.SITE_NAME, " +
				"        B.SITE_LOCATION_LATITUDE, " +
				"        B.SITE_LOCATION_LONGITUDE, " +
				"        B.SITE_LEVEL " +
				"FROM    SCHEDULE A, SITE B " +
				"WHERE   A.SITE_ID = B.SITE_ID " +
				"AND     A.SCH_DAY_OF_WEEK = (strftime('%w','now','localtime')-1) " +
				"AND     datetime(strftime('%Y','now','localtime')||'-'||strftime('%m','now','localtime')||'-'||strftime('%d','now','localtime')||' '||A.SCH_START_HOUR) > datetime('now','localtime') ";
		Cursor crs = db.rawQuery("SELECT count(0) FROM sqlite_master WHERE type='view' AND name='next_class_view';", null);
		if(crs.moveToFirst())
		{
			if(crs.getInt(0) == 0)
			{
				db.execSQL(str_qry);
			}
		}
		crs = null;
	}
	
	/**
	 * This method create, if not exists yet, the view schedule_today_view on database
	 * This view allow see data of today classes
	 * @param myDb Database to work
	 */
	private void createView_ScheduleTodayView(SQLiteDatabase db){
		String str_qry = "CREATE VIEW [schedule_today_view] AS " +
				"SELECT  A.SCH_CLASS_NAME, " +
				"        A.SCH_START_HOUR, " +
				"        A.SCH_END_HOUR, " +
				"        B.SITE_NAME," +
				"        B.SITE_LOCATION_LATITUDE, " +
				"        B.SITE_LOCATION_LONGITUDE, " +
				"        B.SITE_LEVEL " +
				"FROM    SCHEDULE A, SITE B " +
				"WHERE   A.SITE_ID = B.SITE_ID " +
				"AND     A.SCH_DAY_OF_WEEK = strftime('%w','now','localtime') ";
		Cursor crs = db.rawQuery("SELECT count(0) FROM sqlite_master WHERE type='view' AND name='schedule_today_view';", null);
		if(crs.moveToFirst())
		{
			if(crs.getInt(0) == 0)
			{
				db.execSQL(str_qry);
			}
		}
		crs = null;
	}
	
	/**
	 * This method load site types from data_rows_db.xml file 
	 */
	private void insert_SiteType_values(SQLiteDatabase db){
		Resources res = ctx_.getResources();
		String[] array_site_type;
		array_site_type = res.getStringArray(R.array.site_type_name);
		for(int i=0; i < array_site_type.length; i++){
			ContentValues values = new ContentValues();
			values.put("TYP_SITE_ID", i+1);
			values.put("TYP_SITE_NAME", array_site_type[i]);
			values.put("TYP_SITE_DESCRIPTION", array_site_type[i]);
			db.insert("TYPE_SITE", null, values);
			values = null;
		}
	}
	
	/**
	 * This method load sites from data_rows_db.xml file 
	 */
	private void insert_Site_values(SQLiteDatabase db){
		Resources res = ctx_.getResources();
		String[] name_site_type;
		String[] site_latitude_type;
		String[] site_logitude_type;
		name_site_type = res.getStringArray(R.array.name_site);
		site_latitude_type = res.getStringArray(R.array.site_latitude);
		site_logitude_type = res.getStringArray(R.array.site_longitude);
		for(int i=0; i < name_site_type.length; i++){
			ContentValues values = new ContentValues();
			values.put("SITE_ID", i+1);
			values.put("SITE_NAME", name_site_type[i]);
			values.put("TYP_SITE_ID", 1);
			values.put("SITE_LOCATION_LATITUDE", site_latitude_type[i]);
			values.put("SITE_LOCATION_LONGITUDE", site_logitude_type[i]);
			if(name_site_type[i].substring(0,1).equals("A") || name_site_type[i].substring(0,1).equals("B") || name_site_type[i].substring(0,1).equals("C")){
				values.put("SITE_LEVEL", name_site_type[i].substring(1,2));
			}else{
				values.put("SITE_LEVEL", "-1");
			}
			db.insert("SITE", null, values);
			values = null;
		}
	}
	
	/**
	 * This method search on site table for name and if it exists, return id of site
	 * @param str_site_name Is the name of site to search
	 * @return Id of site, if not exists, return -1
	 */
	private String getSiteIdByName(String str_site_name){
		SQLiteDatabase db = this.getWritableDatabase();
		String str_return = "-1";
		Cursor crs = db.rawQuery("SELECT site_id FROM SITE WHERE site_name='"+str_site_name.trim()+"' limit 1;", null);
		if(crs.moveToFirst())
		{
			str_return = crs.getString(0);
		}
		crs = null;
		return str_return;
	}
	
	/**
	 * This method return next value of id based on actual rows of table schedule
	 * @return An integer with next id to use
	 */
	private String getNextScheduleId(){
		SQLiteDatabase db = this.getWritableDatabase();
		String str_next = "0";
		Cursor crs = db.rawQuery("SELECT ifnull(max(SCH_ID),0)+1 FROM SCHEDULE;", null);
		if(crs.moveToFirst())
		{
			str_next = crs.getString(0);
		}
		crs = null;
		return str_next;
	}
	
	/**
	 * This method allow determine if a class, based on day of week, start and end hour, is included on schedule
	 * @param str_day_of_week Day when class happen 
	 * @param str_star_hour Start hour when class happen
	 * @param str_end_hour  End hour when class happen
	 * @return True if class is in conflict with another on schedule otherwise return false
	 */
	public boolean isBetweenClasses(String str_day_of_week, String str_star_hour, String str_end_hour){
		SQLiteDatabase db = this.getWritableDatabase();
		int iCant = 0;
		String str_qry = "SELECT sum(cant) as cant " +
				         "FROM   (   SELECT count(0) as cant " +
				         "           FROM   SCHEDULE " +
				         "           WHERE  datetime(strftime('%Y','now','localtime')||'-'||strftime('%m','now','localtime')||'-'||strftime('%d','now','localtime')||' '||'"+str_star_hour+"') " +
				         "                  between datetime(strftime('%Y','now','localtime')||'-'||strftime('%m','now','localtime')||'-'||strftime('%d','now','localtime')||' '||SCH_START_HOUR) " +
				         "                  and datetime(strftime('%Y','now','localtime')||'-'||strftime('%m','now','localtime')||'-'||strftime('%d','now','localtime')||' '||SCH_END_HOUR) " +
				         "           AND    SCH_DAY_OF_WEEK = "+str_day_of_week+" "+
				         "           UNION ALL" +
				         "           SELECT count(0) as cant " +
				         "           FROM   SCHEDULE " +
				         "           WHERE  datetime(strftime('%Y','now','localtime')||'-'||strftime('%m','now','localtime')||'-'||strftime('%d','now','localtime')||' '||'"+str_end_hour+"') " +
				         "                  between datetime(strftime('%Y','now','localtime')||'-'||strftime('%m','now','localtime')||'-'||strftime('%d','now','localtime')||' '||SCH_START_HOUR) " +
				         "                  and datetime(strftime('%Y','now','localtime')||'-'||strftime('%m','now','localtime')||'-'||strftime('%d','now','localtime')||' '||SCH_END_HOUR)" +
				         "           AND    SCH_DAY_OF_WEEK = "+str_day_of_week+") as tbl";
		Cursor crs = db.rawQuery(str_qry, null);
		if(crs.moveToFirst())
		{
			iCant= crs.getInt(0);
		}
		crs = null;
		if(iCant == 0)
		{
			return false;
		}
		else
		{
			return true;
		}
	}
	
	/**
	 * This method allow to insert a new row on table schedule
	 * @param str_class_name   Name of class
	 * @param str_day_of_week  Day of week when class happen
	 * @param str_star_hour    Start hour when class happen
	 * @param str_end_hour     End hour when class happen
	 * @param str_site_name    Name of site where class happen
	 * @return Row id of last row inserted, if not inserted, return -1 if was a problem with database. INTERNAL ERROR,
	 *         -2 if is a class cross,
	 *         -3 if not found sequence of schedule. INTERNAL ERROR,
	 *         -4 if the site not exists. INTERNAL ERROR 
	 */
	public long insertRowOnSchedule(String str_class_name, String str_day_of_week, String str_star_hour, String str_end_hour, String str_site_name){
		long count_rows = 0;
		String str_site_id = getSiteIdByName(str_site_name);
		String str_sch_id = getNextScheduleId();

		SQLiteDatabase db;
		//Continue if site exists
		if((str_site_id.compareTo("-1") != 0) && (str_sch_id.compareTo("0") != 0) && !isBetweenClasses(str_day_of_week, str_star_hour, str_end_hour)){
			ContentValues values = new ContentValues();
			values.put("SCH_ID", str_sch_id.trim());
			values.put("SCH_CLASS_NAME", str_class_name.trim());
			values.put("SCH_DAY_OF_WEEK", str_day_of_week.trim());
			values.put("SCH_START_HOUR", str_star_hour.trim());
			values.put("SCH_END_HOUR", str_end_hour.trim());
			values.put("SITE_ID", str_site_id.trim());
			db = this.getReadableDatabase();
			count_rows = db.insert("SCHEDULE", null, values);
		}else{
			if(str_site_id.compareTo("-1") == 0){
				count_rows = -4;
			}
			if(str_sch_id.compareTo("0") == 0){
				count_rows = -3;
			}
			if(isBetweenClasses(str_day_of_week, str_star_hour, str_end_hour)){
				count_rows = -2;
			}	
		}
		Log.d(APP_LOG,"EL RESULTADO ES: "+count_rows);
		return count_rows;
	}
	
	/**
	 * This method allow to delete a row from schedule base on class data
	 * @param str_class_name   Name of class to delete
	 * @param str_day_of_week  Day of week when happen class to delete
	 * @param str_star_hour    Start hour when happen class to delete
	 * @return  True if before delete from table, the class not exists otherwise return false 
	 */
	public boolean deleteRowOnSchedule(String str_class_name, String str_day_of_week, String str_star_hour){
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor crs = db.rawQuery("SELECT count(0) FROM SCHEDULE WHERE SCH_CLASS_NAME='"+str_class_name.trim()+"' AND SCH_DAY_OF_WEEK='"+str_day_of_week.trim()+"' AND SCH_START_HOUR='"+str_star_hour.trim()+"';", null);
		if(crs.moveToFirst())
		{
			if(crs.getInt(0) != 0)
			{
				db.execSQL("DELETE FROM SCHEDULE WHERE SCH_CLASS_NAME='"+str_class_name.trim()+"' AND SCH_DAY_OF_WEEK='"+str_day_of_week.trim()+"' AND SCH_START_HOUR='"+str_star_hour.trim()+"';");
			}
		}
		crs = null;
		//Verify if row was deleted
		crs = db.rawQuery("SELECT count(0) FROM SCHEDULE WHERE SCH_CLASS_NAME='"+str_class_name.trim()+"' AND SCH_DAY_OF_WEEK='"+str_day_of_week.trim()+"' AND SCH_START_HOUR='"+str_star_hour.trim()+"';", null);
		while(crs.isAfterLast())
		{
			if(crs.getInt(0) == 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		crs = null;
		return true;
	}
	
	/**
	 * This method allow to delete table schedule completely
	 * @return The value of return is quantity of rows in table schedule before delete it
	 */
	public long deleteAllSchedule()
	{
		long countRows = 0;
		SQLiteDatabase db = this.getReadableDatabase();
		db.execSQL("DELETE FROM SCHEDULE;");
		Cursor crs = db.rawQuery("SELECT count(0) FROM SCHEDULE ;", null);
		if(crs.isAfterLast())
		{
			countRows = crs.getLong(0);
		}
		crs = null;
		return countRows;
	}
	
	/**
	 * This method allow query view schedule_today_view for know what classes take the user today
	 * @return An array of Class_view with data of each class to see today
	 */
	public ArrayList<Class_view> queryScheduleTodayView()
	{
		ArrayList<Class_view> arrayReturn = new ArrayList<Class_view>();
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor crs = db.rawQuery("SELECT * FROM schedule_today_view ORDER BY SCH_START_HOUR;", null);
		while(crs.moveToNext())
		{
			arrayReturn.add(new Class_view(crs.getString(0), crs.getString(1),crs.getString(2), crs.getString(3),crs.getString(4),crs.getString(5),crs.getShort(6)));
		}
		crs = null;
		return arrayReturn;
	}
	
	/**
	 * This method allow query classes take the user for day of week query
	 * @return An array of Class_view with data of each class to this day
	 */
	public ArrayList<Class_view> queryScheduleFor(String str_day_of_week)
	{
		ArrayList<Class_view> arrayReturn = new ArrayList<Class_view>();
		SQLiteDatabase db = this.getReadableDatabase();
		String str_qry = "SELECT  A.SCH_CLASS_NAME, " +
				"        A.SCH_START_HOUR, " +
				"        A.SCH_END_HOUR, " +
				"        B.SITE_NAME," +
				"        B.SITE_LOCATION_LATITUDE, " +
				"        B.SITE_LOCATION_LONGITUDE, " +
				"        B.SITE_LEVEL " +
				"FROM    SCHEDULE A, SITE B " +
				"WHERE   A.SITE_ID = B.SITE_ID " +
				"AND     A.SCH_DAY_OF_WEEK = "+str_day_of_week.trim()+" ORDER BY SCH_START_HOUR";
		Cursor crs = db.rawQuery(str_qry, null);		
		while(crs.moveToNext())
		{
			arrayReturn.add(new Class_view(crs.getString(0), crs.getString(1),crs.getString(2), crs.getString(3),crs.getString(4),crs.getString(5),crs.getShort(6)));
		}
		crs = null;
		return arrayReturn;
	}
	
	/**
	 * This method allow query view class_now_view for know what classes take the user now
	 * @return An array of Class_view with data of each class to see now
	 */
	public ArrayList<Class_view> queryClassNowView()
	{
		ArrayList<Class_view> arrayReturn = new ArrayList<Class_view>();
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor crs = db.rawQuery("SELECT * FROM class_now_view limit 1;", null);
		if(crs.moveToNext())
		{
			arrayReturn.add(new Class_view(crs.getString(0), crs.getString(1),crs.getString(2), crs.getString(3),crs.getString(4),crs.getString(5),crs.getShort(6)));
		}
		crs = null;
		return arrayReturn;
	}
	
	/**
	 * This method allow query view class_now_view for know what next classes take the user
	 * @return An array of Class_view with data of each next class to see
	 */
	public ArrayList<Class_view> queryNextClassView()
	{
		ArrayList<Class_view> arrayReturn = new ArrayList<Class_view>();
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor crs = db.rawQuery("SELECT * FROM next_class_view limit 1;", null);
		if(crs.moveToFirst())
		{
			arrayReturn.add(new Class_view(crs.getString(0), crs.getString(1),crs.getString(2), crs.getString(3),crs.getString(4),crs.getString(5),crs.getShort(6)));
		}
		crs = null;
		return arrayReturn;
	}
	
	/**
	 * This method query a list of start hour on schedule by day of week
	 * @param str_day_of_week Day of week for query
	 * @return An ArrayList with start hours on schedule for day to query
	 */
	public ArrayList<String> queryStartHourByDay(String str_day_of_week)
	{
		ArrayList<String> arrayReturn = new ArrayList<String>();
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor crs = db.rawQuery("SELECT SCH_START_HOUR FROM SCHEDULE WHERE SCH_DAY_OF_WEEK = "+str_day_of_week.trim()+" ORDER BY sch_start_hour;", null);
		while(crs.moveToNext())
		{
			arrayReturn.add(crs.getString(0));
		}
		crs = null;
		return arrayReturn;
	}
	
	/**
	 * In this method make a query for class name for a day week and start hour 
	 * @param str_day_of_week Day of week for query
	 * @param str_start_hour Start hour for query
	 * @return One String with first value returned by query to database 
	 */
	public String queryClassNameByDayAndStartHour(String str_day_of_week, String str_start_hour)
	{
		String strReturn = "";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor crs = db.rawQuery("SELECT SCH_CLASS_NAME FROM SCHEDULE WHERE SCH_DAY_OF_WEEK = "+str_day_of_week.trim()+" AND SCH_START_HOUR ='"+str_start_hour.trim()+"' limit 1;", null);
		if(crs.moveToNext())
		{
			strReturn= crs.getString(0);
		}
		crs = null;
		return strReturn;
	}
}
